<?php

declare(strict_types=1);

namespace App\CustHub\Service;

use App\Common\Constants\ErrorCode;
use App\CustHub\Constants\ContactGenderConstants;
use App\CustHub\Constants\ContactSourceConstants;
use App\CustHub\Mapper\ChChatRecordMapper;
use App\CustHub\Mapper\ChContactMapper;

use App\CustHub\Mapper\ChRobotMapper;
use App\CustHub\Mapper\ChRoomMapper;
use App\CustHub\Mapper\ChRoomMemberMapper;
use App\CustHub\Mapper\SystemUpdateLogMapper;
use App\CustHub\Model\ChTalkRecord;
use App\CustHub\Model\ChTalkSession;
use App\Exception\BusinessException;
use Carbon\Carbon;
use Hyperf\DbConnection\Db;
use Hyperf\Di\Annotation\Inject;
use Mine\Abstracts\AbstractService;
use function Hyperf\Collection\data_get;

class StatisticsService extends AbstractService
{

    #[Inject]
    protected ChContactMapper $ChContactMapper;

    #[Inject]
    protected ChRoomMapper $ChRoomMapper;

    #[Inject]
    protected ChTalkRecord $ChTalkRecord;

    #[Inject]
    protected ChRoomMemberMapper $ChRoomMemberMapper;

    #[Inject]
    protected ChRobotMapper $ChRobotMapper;

    #[Inject]
    protected ChChatRecordMapper $ChChatRecordMapper;

    #[Inject]
    protected SystemUpdateLogMapper $SystemUpdateLog;

    protected $cacheTtl = 7200;
    public function calendar($params)
    {
        $date=$params['date'];
        $carbonDate = Carbon::parse($date);
        $carbonDate->year;
        $carbonDate->month;
        $list = getDatesOfMonth($carbonDate->year,$carbonDate->month);
        var_dump($list);
    }

    public function getUpdateLog($params)
    {
        $list=$this->SystemUpdateLog->model::query()->orderBy('id', 'desc')->paginate((int)$params['page_size']);
        $list->getCollection()->transform(function ($item) {
            $item['created_at_text']=$item->created_at->format('Y年m月d日');
            return $item;
        });
        return $this->SystemUpdateLog->setPaginate($list);
    }

    public function indexChartData($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $range=data_get($params,'range');
        $time=data_get($params,'time','last_7_days');
        $cache_name  = 'indexCustomData:' . $mainUid.$uid.$time;
        $data=redis()->get($cache_name);
        if(!empty($data)){
            return $data;
        }
        if($time){
            $range=$this->getDataRange($time);
        }
        $data=[];
        $contactBuild = $this->ChContactMapper->model::query()->where('main_uid',$mainUid);
        $data['chart']=$this->getChartDataIndex($contactBuild,$range[0],$range[1]);
        redis()->set($cache_name,$data,$this->cacheTtl);
        return $data;
    }

    public function indexRoomChartData($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $range=data_get($params,'range');
        $time=data_get($params,'time','last_7_days');
        $cache_name  = 'indexRoomChartData:' . $mainUid.$uid.$time;
        $data=redis()->get($cache_name);
        if(empty($range) && !empty($data)){
            return $data;
        }
        if($time){
            $range=$this->getDataRange($time);
        }
        $data=[];
        $data['chart']=$this->getChartRoomIndexData($mainUid,$range[0],$range[1]);
        redis()->set($cache_name,$data,$this->cacheTtl);
        return $data;
    }

    public function indexRoomData($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $range=data_get($params,'range');
        //$time=data_get($params,'time','last_7_days');
        $cache_name  = 'indexRoomData:' . $mainUid.$uid;
        $data=redis()->get($cache_name);
        if(empty($range) && !empty($data)){
            return $data;
        }
        $roomBuild = $this->ChRoomMapper->model::query()->where('main_uid',$mainUid);

        //群总数
        $total=$this->ChRoomMapper->model::query()->where('main_uid',$mainUid)->count();
        $yesterday = Carbon::yesterday()->format('Y-m-d');
        $today = Carbon::today()->format('Y-m-d');
        $todayStart = strtotime($today . ' 00:00:00');
        $todayEnd = strtotime($today . ' 23:59:59');
        $yesterdayStart = strtotime($yesterday . ' 00:00:00');
        $yesterdayEnd = strtotime($yesterday . ' 23:59:59');
        $lastWeekEndDate = Carbon::now()->subWeek()->endOfWeek();
        $lastWeekStartDate = Carbon::now()->subWeek()->startOfWeek();
        $lastMonthEndDate = Carbon::now()->subMonth()->endOfMonth();
        $lastMonthStartDate = Carbon::now()->subMonth()->startOfMonth();
        $endYesterdayTotal = $this->ChRoomMapper->model::query()->where('main_uid',$mainUid)->whereBetween('create_time', [0, $yesterdayEnd])->count();
        $endLastWeekTotal = $this->ChRoomMapper->model::query()->where('main_uid',$mainUid)->whereBetween('create_time', [0, $lastWeekEndDate->getTimestamp()])->count();
        $endLastMonthTotal = $this->ChRoomMapper->model::query()->where('main_uid',$mainUid)->whereBetween('create_time', [0, $lastMonthEndDate->getTimestamp()])->count();
        //群成员总数
        $memberTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->where('ch_room_member.is_out',0)
            ->count();
        $endYesterdayMemberTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('join_time', [0, $yesterdayEnd])
            ->where('ch_room_member.is_out',0)
            ->count();
        $endLastWeekMemberTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('join_time', [0, $lastWeekEndDate->getTimestamp()])
            ->where('ch_room_member.is_out',0)
            ->count();
        $endLastMonthMemberTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('join_time', [0, $lastMonthEndDate->getTimestamp()])
            ->where('ch_room_member.is_out',0)
            ->count();
        //今日新增
        $todayTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('join_time', [$todayStart, $todayEnd])
            ->where('ch_room_member.is_out',0)
            ->count();
        $yesterdayMemberTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('join_time', [$yesterdayStart, $yesterdayEnd])
            ->where('ch_room_member.is_out',0)
            ->count();
        $lastWeekMemberTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('join_time', [$lastWeekStartDate->getTimestamp(), $lastWeekEndDate->getTimestamp()])
            ->where('ch_room_member.is_out',0)
            ->count();
        $lastMonthMemberTotal=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('join_time', [$lastMonthStartDate->getTimestamp(), $lastMonthEndDate->getTimestamp()])
            ->where('ch_room_member.is_out',0)
            ->count();

        $data=[];
        $data['room']=[
            'total'=>$total,
            'yesterday_total'=>$endYesterdayTotal,
            'rate_day'=>$endYesterdayTotal?(float)bcdiv((string)(($total-$endYesterdayTotal)*100), (string)$endYesterdayTotal,2):0,
            'rate_week'=>$endLastWeekTotal?(float)bcdiv((string)(($total-$endLastWeekTotal)*100), (string)$endLastWeekTotal,2):0,
            'rate_month'=>$endLastMonthTotal?(float)bcdiv((string)(($total-$endLastMonthTotal)*100), (string)$endLastMonthTotal,2):0,
        ];
        $data['room_member']=[
            'total'=>$memberTotal,
            'yesterday_total'=>$endYesterdayMemberTotal,
            'rate_day'=>$endYesterdayMemberTotal?(float)bcdiv((string)(($memberTotal-$endYesterdayMemberTotal)*100), (string)$endYesterdayMemberTotal,2):0,
            'rate_week'=>$endLastWeekMemberTotal?(float)bcdiv((string)(($memberTotal-$endLastWeekMemberTotal)*100), (string)$endLastWeekMemberTotal,2):0,
            'rate_month'=>$endLastMonthMemberTotal?(float)bcdiv((string)(($memberTotal-$endLastMonthMemberTotal)*100), (string)$endLastMonthMemberTotal,2):0,
        ];
        $data['room_new_member']=[
            'total'=>$todayTotal,
            'yesterday_total'=>$yesterdayMemberTotal,
            'rate_day'=>$endYesterdayMemberTotal?(float)bcdiv((string)($yesterdayMemberTotal*100), (string)$endYesterdayMemberTotal,2):0,
            'rate_week'=>$endLastWeekMemberTotal?(float)bcdiv((string)($lastWeekMemberTotal*100), (string)$endLastWeekMemberTotal,2):0,
            'rate_month'=>$endLastMonthMemberTotal?(float)bcdiv((string)($lastMonthMemberTotal*100), (string)$endLastMonthMemberTotal,2):0,
        ];
        //群人数分布
        $data['member_chart']=$this->getChartRoomDataIndex($roomBuild);
        //近七天群活跃排行
        $data['room_active_rank']=$this->getChartRoomActiveRank($mainUid);
        (redis())->set($cache_name,$data,$this->cacheTtl);
        return $data;
    }

    public function indexDataCustom(array $params)
    {
        $mainUid=$params['main_uid'];
        //$time=data_get($params,'time','last_7_days');
        $cache_name  = 'indexCustomData:' . $mainUid;
        $data=redis()->get($cache_name);
        if(empty($range) && !empty($data)){
            return $data;
        }

        $data=[];
        $contactBuild = $this->ChContactMapper->model::query()->where('main_uid',$mainUid);
        //全部客户
        $total=$this->ChContactMapper->model::query()->where('main_uid',$mainUid)->count();
        $yesterday = Carbon::yesterday()->format('Y-m-d');
        $today = Carbon::today()->format('Y-m-d');
        $todayStart = strtotime($today . ' 00:00:00');
        $todayEnd = strtotime($today . ' 23:59:59');
        $yesterdayStart = strtotime($yesterday . ' 00:00:00');
        $yesterdayEnd = strtotime($yesterday . ' 23:59:59');
        $lastWeekEndDate = Carbon::now()->subWeek()->endOfWeek();
        $lastWeekStartDate = Carbon::now()->subWeek()->startOfWeek();
        $lastMonthEndDate = Carbon::now()->subMonth()->endOfMonth();
        $lastMonthStartDate = Carbon::now()->subMonth()->startOfMonth();
        $endYesterdayTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('add_customer_time', [0, $yesterdayEnd])->count();
        $endLastWeekTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('add_customer_time', [0, $lastWeekEndDate->getTimestamp()])->count();
        $endLastMonthTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('add_customer_time', [0, $lastMonthEndDate->getTimestamp()])->count();
        //今日新增客户
        $todayTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('add_customer_time', [$todayStart, $todayEnd])->count();
        $yesterdayTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('add_customer_time', [$yesterdayStart, $yesterdayEnd])->count();
        $lastWeekTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('add_customer_time', [$lastWeekStartDate->getTimestamp(), $lastWeekEndDate->getTimestamp()])->count();
        $lastMonthTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('add_customer_time', [$lastMonthStartDate->getTimestamp(), $lastMonthEndDate->getTimestamp()])->count();
        //今日流失用户
        $todayLossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('friend_loss_time', [datetime($todayStart), datetime($todayEnd)])->count();
        //$lossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->where('status','<>',1)->count();
        $yesterdayLossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('friend_loss_time', [datetime($yesterdayStart), datetime($yesterdayEnd)])->count();
        $lastWeekLossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('friend_loss_time', [$lastWeekStartDate, $lastWeekEndDate])->count();
        $lastMonthLossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('friend_loss_time', [$lastMonthStartDate, $lastMonthEndDate])->count();
        $endYesterdayLossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('friend_loss_time', [0, datetime($yesterdayEnd)])->count();
        $endLastWeekLossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('friend_loss_time', [0, $lastWeekEndDate])->count();
        $endLastMonthLossTotal = $this->ChContactMapper->model::query()->where('main_uid',$mainUid)->whereBetween('friend_loss_time', [0, $lastMonthEndDate])->count();

        $data['custom']=[
            'total'=>$total,
            'yesterday_total'=>$endYesterdayTotal,
            'rate_day'=>$endYesterdayTotal?(float)bcdiv((string)(($total-$endYesterdayTotal)*100), (string)$endYesterdayTotal,2):0,
            'rate_week'=>$endLastWeekTotal?(float)bcdiv((string)(($total-$endLastWeekTotal)*100), (string)$endLastWeekTotal,2):0,
            'rate_month'=>$endLastMonthTotal?(float)bcdiv((string)(($total-$endLastMonthTotal)*100), (string)$endLastMonthTotal,2):0,
        ];
        $data['custom_today']=[
            'total'=>$todayTotal,
            'yesterday_total'=>$yesterdayTotal,
            'rate_day'=>$endYesterdayTotal?(float)bcdiv((string)($yesterdayTotal*100), (string)$endYesterdayTotal,2):0,
            'rate_week'=>$endLastWeekTotal?(float)bcdiv((string)($lastWeekTotal*100), (string)$endLastWeekTotal,2):0,
            'rate_month'=>$endLastMonthTotal?(float)bcdiv((string)($lastMonthTotal*100), (string)$endLastMonthTotal,2):0,
        ];
        $data['loss']=[
            'total'=>$todayLossTotal,
            'yesterday_total'=>$yesterdayLossTotal,
            'rate_day'=>$endYesterdayLossTotal?(float)bcdiv((string)($yesterdayLossTotal*100), (string)$endYesterdayLossTotal,2):0,
            'rate_week'=>$endLastWeekLossTotal?(float)bcdiv((string)($lastWeekLossTotal*100), (string)$endLastWeekLossTotal,2):0,
            'rate_month'=>$endLastMonthLossTotal?(float)bcdiv((string)($lastMonthLossTotal*100), (string)$endLastMonthLossTotal,2):0,
        ];

        $sexQuery=clone $contactBuild;
        $sourceQuery=clone $contactBuild;
        //查询渠道分布
        $contactSourceData=$this->getContactSourceData($sourceQuery);
        $data['sourceConstants']=$contactSourceData['source_constants'];
        $data['source_data']=$contactSourceData['source_data'];

        //查询性别分布
        $data['GenderConstants']=ContactGenderConstants::getOptions();
        $sexData=$sexQuery->selectRaw(
            "gender,count(1) as total",
        )->groupBy('gender')->get()->toArray();
        $data['sex_data']=$sexData;

        //查询标签分布
        $tagsData=$this->getCropTagData($mainUid);
        $data['tagsConstants']=$tagsData['tags_constants'];
        $data['tags_data']=$tagsData['tags_data'];
        redis()->set($cache_name,$data,$this->cacheTtl);
        return $data;
    }

    public function robotStatistics($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $robotId=data_get($params,'robot_id');
        $range=data_get($params,'range');
        $time=data_get($params,'time','today');
        //$cache_name  = 'robotStatistics1:' . md5($mainUid.$uid.$robotId.$time);
        //$data=redis()->get($cache_name);
        if(empty($range) && !empty($data)){
            return $data;
        }
        if($range){
            if(Carbon::parse($range[0])->diffInDays(Carbon::parse($range[1])) > 30){
                throw new BusinessException(ErrorCode::PARAMS_ERROR,'时间范围不能超过30天');
            }
        }
        if($time){
            $range=$this->getDataRange($time);
        }
        $where =[
            ['main_uid',$params['main_uid']],
        ];
        if($robotId){
            $where[]=['id',$robotId];
        }
        $paginate = $this->ChRobotMapper->model::query()->where($where)
            ->select(["nickname","corp_name","avatar","id"])
            ->paginate((int)$params['page_size']);
        $paginate->getCollection()->transform(function ($item)use($params,$range) {
            $send_total=$this->ChTalkRecord::query()->where('robot_id',$item['id'])->whereBetween('created_at',[$range[0],$range[1]])->count();
            $talkData = ChTalkSession::query()->where('robot_id',$item['id'])->whereBetween('created_at',[$range[0],$range[1]])
                ->selectRaw(
                "SUM(CASE WHEN is_robot = 1 THEN 1 ELSE 0 END) as robot_send_total,
             SUM(CASE WHEN is_robot = 0 THEN 1 ELSE 0 END) as custom_send_total,
             SUM(CASE WHEN contact_num > 0 THEN 1 ELSE 0 END) as reply_total,
             AVG(CASE WHEN contact_first_reply is not null THEN contact_first_reply ELSE NULL END) as reply_time,
             count(1) as chat_total"
            )->first();

            //查询时间内统计
            $startTimeInt= strtotime($range[0]);
            $endTimeInt= strtotime($range[1]);
            $ContactData=$this->ChContactMapper->model::query()->where('robot_id',$item['robot_id'])->selectRaw(
                "SUM(CASE WHEN add_customer_time < $endTimeInt then 1 else 0 end) AS total,
                SUM(CASE WHEN add_customer_time between '$startTimeInt' and '$endTimeInt' then 1 else 0 end) AS new_total,
            SUM(CASE WHEN last_send_msg_time between '$range[0]' and '$range[1]' then 1 else 0 end) AS active_total,
            SUM(CASE WHEN friend_loss_time < '$range[0]' and status = 3 then 1 else 0 end) AS loss_total,
            SUM(CASE WHEN friend_loss_time between '$range[0]' and '$range[1]' and status = 3 then 1 else 0 end) AS new_loss_total",
            )->first()->toArray();

            $item->custom_statistics=[
                "total"=>$ContactData['total']?:0,
                "active_total"=>$ContactData['active_total']?:0,
                "new_total"=>$ContactData['new_total']?:0,
                "loss_total"=>$ContactData['loss_total']?:0,
                "new_loss"=>$ContactData['new_loss_total']?:0,
            ];
            $item->chat_statistics=[
                "chat_total"=>$talkData['chat_total']?:0,
                "room_total"=>$talkData['chat_total']?:0,
                "robot_send_total"=>$talkData['robot_send_total']?:0,
                "custom_send_total"=>$talkData['custom_send_total']?:0,
                "send_total"=>$send_total,
                "reply_total"=>$talkData['reply_total']?:0,
                "reply_rate"=>$talkData['chat_total']?bcdiv((string)($talkData['reply_total']*100),(string)$talkData['chat_total'],2):0,
                "reply_time"=>$talkData['reply_time']?:0,
            ];
            return $item;
        });
        return $this->ChContactMapper->setPaginate($paginate);
    }

    public function chatStatisticsByDay($params)
    {
        $talkRecordBuild = ChTalkSession::query()->where('main_uid',$params['main_uid']);
        $range=data_get($params,'range');

        $paginate = $talkRecordBuild->selectRaw(
            "DATE_FORMAT(created_at, '%Y-%m-%d') as date,
             SUM(CASE WHEN is_robot = 1 THEN 1 ELSE 0 END) as robot_chat_total,
             SUM(CASE WHEN is_robot = 0 THEN 1 ELSE 0 END) as custom_chat_total,
             SUM(CASE WHEN contact_num > 0 THEN 1 ELSE 0 END) as reply_total,
             AVG(CASE WHEN contact_first_reply is not null THEN contact_first_reply ELSE NULL END) as reply_time,
             count(1) as chat_total"
        )   ->when($range,function ($query)use($range){
                $query->whereBetween('created_at',[$range[0],$range[1]]);
             })
            ->groupBy("date")
            ->orderBy("date",'desc')
            ->paginate((int)$params['page_size']);
        $paginate->getCollection()->transform(function ($item,$range)use($params){
            $reply_rate=$item['chat_total']?bcdiv((string)($item['reply_total']*100),(string)$item['chat_total'],2):0;
            $item->reply_rate=$reply_rate;
            $item->send_total=$this->ChTalkRecord::query()->where('main_uid',$params['main_uid'])->whereRaw("DATE_FORMAT(created_at, '%Y-%m-%d') = ".$item['date'])->count();;
            return $item;
        });
        return $this->ChContactMapper->setPaginate($paginate);
    }

    public function getChatStatistics($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $robotId=data_get($params,'robot_id');
        $range=data_get($params,'range');
        $time=data_get($params,'time','today');
        $cache_name  = 'getChatStatistics:' . md5($mainUid.$uid.$robotId.$time);
        $data=redis()->get($cache_name);
        if(empty($range) && !empty($data)){
            return $data;
        }
        if($range){
            if(Carbon::parse($range[0])->diffInDays(Carbon::parse($range[1])) > 30){
                throw new BusinessException(ErrorCode::PARAMS_ERROR,'时间范围不能超过30天');
            }
        }
        if($time){
            $range=$this->getDataRange($time);
        }
        $where =[];
        if($robotId){
            $where[]=['robot_id',$robotId];
        }
        $talkRecordBuild = $this->ChTalkRecord::query()->where('main_uid',$params['main_uid'])->where($where)->whereBetween('created_at',[$range[0],$range[1]]);
        $send_total=$talkRecordBuild->count();
        $talkRecordBuild = ChTalkSession::query()->where('main_uid',$params['main_uid'])->where($where)->whereBetween('created_at',[$range[0],$range[1]]);

        $talkData = $talkRecordBuild->selectRaw(
            "SUM(CASE WHEN is_robot = 1 THEN 1 ELSE 0 END) as robot_chat_total,
             SUM(CASE WHEN is_robot = 0 THEN 1 ELSE 0 END) as custom_chat_total,
             SUM(CASE WHEN contact_num > 0 THEN 1 ELSE 0 END) as reply_total,
             AVG(CASE WHEN contact_first_reply is not null THEN contact_first_reply ELSE NULL END) as reply_time,
             count(1) as chat_total"
        )->first();
        $data=[
            'robot_chat_total'=>$talkData['robot_chat_total']?:0,
            'custom_chat_total'=>$talkData['custom_chat_total']?:0,
            'chat_total'=>$talkData['chat_total']?:0,
            'reply_total'=>$talkData['reply_total']?:0,
            'reply_time'=>$talkData['robot_chat_total']?:0,
            'send_total'=>$send_total?:0,
            'reply_rate'=>$talkData['chat_total']?bcdiv((string)($talkData['reply_total']*100),(string)$talkData['chat_total'],2):0,
        ];
        $chartData=$this->getChatRoomData($params,$range[0],$range[1]);
        $data['chart_data']=$chartData;
        return $data;
    }

    public function getChatReplyStatistics($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $robotId=data_get($params,'robot_id');
        $range=data_get($params,'range');
        $time=data_get($params,'time','today');
        if($range){
            if(Carbon::parse($range[0])->diffInDays(Carbon::parse($range[1])) > 30){
                throw new BusinessException(ErrorCode::PARAMS_ERROR,'时间范围不能超过30天');
            }
        }
        if($time){
            $range=$this->getDataRange($time);
        }
        $where=[];
        if($robotId){
            $where[]=['robot_id',$robotId];
        }
        $talkRecordBuild = ChTalkSession::query()->where('main_uid',$params['main_uid'])->where($where)->whereBetween('created_at',[$range[0],$range[1]]);

        $paginate = $talkRecordBuild->selectRaw(
            "DATE_FORMAT(created_at, '%Y-%m-%d') as date,
            SUM(CASE WHEN contact_first_reply is not null and  contact_first_reply < 720  then 1 else 0 end) as reply_12,
            SUM(CASE WHEN contact_first_reply is not null and  contact_first_reply < 1440  then 1 else 0 end) as reply_24,
            count(1) as chat_total"
        )->groupBy('date')->orderBy('date','desc')
            ->paginate((int)$params['page_size']);
        $paginate->getCollection()->transform(function ($item) {
            $item->reply_12=0;
            $item->reply_24=0;
            $item->reply_rate_12=$item['chat_total']>0?bcdiv((string)($item['reply_12']*100),(string)$item['chat_total'],2):0;
            $item->reply_rate_24=$item['chat_total']>0?bcdiv((string)($item['reply_24']*100),(string)$item['chat_total'],2):0;
            return $item;
        });
        return $this->ChContactMapper->setPaginate($paginate);
    }

    public function getChatReplyStatisticsByDay($params)
    {
        $date=data_get($params,'date');
        $where = [
            ['main_uid',$params['main_uid']]
        ];
        if(!empty($params['robot_id'])){
            $where[]=['robot_id',$params['robot_id']];
        }

        $build = ChTalkSession::query()->where($where)->whereRaw("DATE_FORMAT(created_at, '%Y-%m-%d') = '$date'");
        $paginate=$build->with(['robot'=>function ($query) {
            $query->select(['id','nickname','avatar','corp_name','login_status']);
        }])->when(!empty($params['reply_type']),function ($query)use($params) {
            $query->whereNotNull('contact_first_reply');
            if($params['reply_type'] == 1){
                $query->where('contact_first_reply','<',720);
            }else{
                $query->where('contact_first_reply','<',1440);
            }
        })->paginate((int)$params['page_size']);
        $paginate->getCollection()->transform(function ($item) {
            $reply_12=false;
            $reply_24=false;
            if(!is_null($item->contact_first_reply ) && $item->contact_first_reply < 720){
                $reply_12=true;
            }
            if(!is_null($item->contact_first_reply ) && $item->contact_first_reply < 720){
                $reply_24=true;
            }
            $item->reply_12=$reply_12;
            $item->reply_24=$reply_24;
            $user_id=$item->receiver;
//            if($item['is_robot']){
//                $user_id=$item->receiver;
//            }else{
//                $user_id=$item->sender;
//            }
            $item->contact=$this->ChContactMapper->findByRobotIdAndUserId((int)$item->robot_id,(string)$user_id);
            return $item;
        });
        return $this->ChChatRecordMapper->setPaginate($paginate);
    }


    public function getRoomStatistics($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $robotId=data_get($params,'robot_id');
        $range=data_get($params,'range');
        $time=data_get($params,'time','today');
        $cache_name  = 'getRoomStatistics:' . $mainUid.$uid.$robotId.$time;
        $data=redis()->get($cache_name);
        if(empty($range) && !empty($data)){
            return $data;
        }
        if($range){
            if(Carbon::parse($range[0])->diffInDays(Carbon::parse($range[1])) > 12){
                throw new BusinessException(ErrorCode::PARAMS_ERROR,'时间范围不能超过12个月');
            }
        }
        if($time){
            $range=$this->getDataRange($time);
        }
        $startInt=strtotime($range[0]);
        $endInt=strtotime($range[1]);
        $roomBuild = $this->ChRoomMapper->model::query()->where('main_uid',$mainUid);
        $roomMemberData=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->where('ch_room.main_uid',$mainUid)
            ->selectRaw(
                "SUM(CASE WHEN join_time < '$startInt' then 1 else 0 end) as room_member_total,
                SUM(CASE WHEN out_time between '{$range[0]}' and '{$range[1]}' then 1 else 0 end) AS new_out_total,
                SUM(CASE WHEN join_time between '$startInt' and '$endInt' then 1 else 0 end) AS new_member_total",
            )->first()->toArray();

        $data=[
            'new_member_total'=>$roomMemberData?$roomMemberData['new_member_total']:0,
            'new_out_total'=>$roomMemberData?$roomMemberData['new_out_total']:0,
            'new_room_total'=>$this->ChRoomMapper->model::query()->where('main_uid',$mainUid)->whereBetween('create_time',[$startInt,$endInt])->count(),
            'room_total'=>$this->ChRoomMapper->model::query()->where('main_uid',$mainUid)->whereBetween('create_time',[0,$endInt])->count(),
            'member_total'=>$roomMemberData?$roomMemberData['room_member_total']:0,
        ];
        $chartData=$this->getChartRoomData($mainUid,$range[0],$range[1]);
        $data['chart_data']=$chartData;
        return $data;
    }

    public function getCustomerStatistics($params)
    {
        $mainUid=$params['main_uid'];
        $uid=$params['uid'];
        $robotId=data_get($params,'robot_id');
        $range=data_get($params,'range');
        $time=data_get($params,'time','today');
        $cache_name  = 'getCustomerStatistics222:' . md5($mainUid.$uid.$robotId.$time);
        $data=redis()->get($cache_name);
        if(empty($range) && !empty($data)){
            return $data;
        }
        $data=[];
        if($range){
            if(Carbon::parse($range[0])->diffInDays(Carbon::parse($range[1])) > 12){
                throw new BusinessException(ErrorCode::PARAMS_ERROR,'时间范围不能超过12个月');
            }
        }
        if($time){
            $range=$this->getDataRange($time);
        }
        //客户总数
        $custom_total=$this->ChContactMapper->model::query()->where('main_uid',$mainUid)->count();
        //流失总数
        $loss_total=$this->ChContactMapper->model::query()->where('main_uid',$mainUid)->where('status',3)->count();
        //进群数量
        $in_room_total=$this->ChRoomMemberMapper->model::query()
            ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
            ->join('ch_contact', function($join) use ($mainUid,$range,$robotId) {
                $join->on('ch_contact.user_id', '=', 'ch_room_member.user_id')
                    ->where('ch_contact.main_uid', $mainUid)
                    ->when($robotId,function ($query) use ($robotId) {
                        $query->where('ch_room_member.robot_id',$robotId);
                    })
                    ->whereBetween('ch_contact.add_customer_time',[strtotime($range[0]),strtotime($range[1])]);
            })
            ->where('ch_room.main_uid',$mainUid)
            ->whereBetween('ch_room_member.join_time', [strtotime($range[0]),strtotime($range[1])])
            ->when($robotId,function ($query) use ($robotId) {
                $query->where('ch_room_member.robot_id',$robotId);
            })
            ->where('ch_room_member.is_friend',1)
            ->where('ch_room_member.is_out',0)
            ->count(Db::raw('DISTINCT ch_room_member.user_id'));
        //获取客户数统计
        $contactBuild = $this->ChContactMapper->model::query()->where('main_uid',$mainUid);
        $sourceQuery = clone $contactBuild;
        $sexQuery = clone $contactBuild;
        $chatQuery = clone $contactBuild;
        $contactBuild->whereBetween('create_time',[strtotime($range[0]),strtotime($range[1])]);
        if($uid){
            $contactBuild->where('uid',$uid);
        }
        if($robotId){
            $contactBuild->where('robot_id',$robotId);
        }
        //查询性别分布
        $data['GenderConstants']=ContactGenderConstants::getOptions();
        $sexData=$sexQuery->selectRaw(
            "gender,count(1) as total",
        )->groupBy('gender')->get()->toArray();
        $data['sex_data']=$sexData;
        //查询渠道分布
        $contactSourceData=$this->getContactSourceData($sourceQuery);
        $data['sourceConstants']=$contactSourceData['source_constants'];
        $data['source_data']=$contactSourceData['source_data'];

        //查询时间内统计
        $startTimeInt= strtotime($range[0]);
        $endTimeInt= strtotime($range[1]);
        $ContactData=$contactBuild->selectRaw(
            "SUM(CASE WHEN add_customer_time between '$startTimeInt' and '$endTimeInt' then 1 else 0 end) AS new_total,
            SUM(CASE WHEN last_send_msg_time between '$range[0]' and '$range[1]' then 1 else 0 end) AS active_total,
            SUM(CASE WHEN friend_loss_time < '$range[0]' and status = 3 then 1 else 0 end) AS loss_total,
            SUM(CASE WHEN friend_loss_time between '$range[0]' and '$range[1]' and status = 3 then 1 else 0 end) AS new_loss_total",
        )->first()->toArray();
        $data['active_total']=(int)$ContactData['active_total']?:0;
        $data['new_total']=(int)$ContactData['new_total']?:0;
        $data['in_room_total']=(int)$in_room_total;
        $data['in_room_rate']=$ContactData['new_total']>0?(float)bcdiv((string)($in_room_total*100),(string)$ContactData['new_total'],2):0;
        $data['new_loss_total']=(int)$ContactData['new_loss_total']?:0;
        $data['custom_total']=(int)$custom_total;
        $data['loss_total']=(int)$loss_total;
        //获取图表数据
        $chartData=$this->getChartData($chatQuery,$range[0],$range[1]);
        $data['chart_data']=$chartData;
        redis()->set($cache_name,$data,$this->cacheTtl);
        return $data;
    }


    public function lcStatisticsByDay($params)
    {
        $contactBuild = $this->ChContactMapper->model::query()->where('main_uid',$params['main_uid']);
        $paginate=$contactBuild->selectRaw(
            "FROM_UNIXTIME(add_customer_time, '%Y-%m-%d') as date,
            count(*) as new_total,
            SUM(CASE WHEN status = 3 then 1 else 0 end) AS new_loss_total"
        )   ->groupBy("date")
            ->orderBy("date",'desc')
            ->paginate((int)$params['page_size']);
        $paginate->getCollection()->transform(function ($item)use($params){
            $date = $item->date;
            $dateCarbon = Carbon::parse($date);
            // 明天
            $tomorrow = $dateCarbon->copy()->addDay()->endOfDay()->format('Y-m-d H:i:s');
            // 三天后
            $threeDaysLater = $dateCarbon->copy()->addDays(3)->endOfDay()->format('Y-m-d H:i:s');
            // 七天后
            $sevenDaysLater = $dateCarbon->copy()->addDays(7)->endOfDay()->format('Y-m-d H:i:s');
            // 14天后
            $fourteenDaysLater = $dateCarbon->copy()->addDays(14)->endOfDay()->format('Y-m-d H:i:s');
            // 30天后
            $thirtyDaysLater = $dateCarbon->copy()->addDays(30)->endOfDay()->format('Y-m-d H:i:s');
            $tomorrow_loss_total=$this->ChContactMapper->model::query()->where('main_uid',$params['main_uid'])->whereRaw("FROM_UNIXTIME(add_customer_time, '%Y-%m-%d') = '$date' and friend_loss_time is not null and friend_loss_time <= '$tomorrow'")->count();
            $day3_loss_total=$this->ChContactMapper->model::query()->where('main_uid',$params['main_uid'])->whereRaw("FROM_UNIXTIME(add_customer_time, '%Y-%m-%d') = '$date' and friend_loss_time is not null and friend_loss_time <= '$threeDaysLater'")->count();
            $day7_loss_total=$this->ChContactMapper->model::query()->where('main_uid',$params['main_uid'])->whereRaw("FROM_UNIXTIME(add_customer_time, '%Y-%m-%d') = '$date' and friend_loss_time is not null and friend_loss_time <= '$sevenDaysLater'")->count();
            $day14_loss_total=$this->ChContactMapper->model::query()->where('main_uid',$params['main_uid'])->whereRaw("FROM_UNIXTIME(add_customer_time, '%Y-%m-%d') = '$date' and friend_loss_time is not null and friend_loss_time <= '$fourteenDaysLater'")->count();
            $day30_loss_total=$this->ChContactMapper->model::query()->where('main_uid',$params['main_uid'])->whereRaw("FROM_UNIXTIME(add_customer_time, '%Y-%m-%d') = '$date' and friend_loss_time is not null and friend_loss_time <= '$thirtyDaysLater'")->count();

            $item->tomorrow_total=$item->new_total-$tomorrow_loss_total;
            $item->day3_total=$item->new_total-$day3_loss_total;
            $item->day7_total=$item->new_total-$day7_loss_total;
            $item->day14_total=$item->new_total-$day14_loss_total;
            $item->day30_total=$item->new_total-$day30_loss_total;

            $item->loss_rate=$item->new_total?(float)bcdiv((string)($item->new_loss_total*100), (string)$item->new_total,2):0;
            $item->tomorrow_rate=$item->new_total?(float)bcdiv((string)($item->tomorrow_total*100), (string)$item->new_total,2):0;;
            $item->day3_rate=$item->new_total?(float)bcdiv((string)($item->day3_total*100), (string)$item->new_total,2):0;;
            $item->day7_rate=$item->new_total?(float)bcdiv((string)($item->day7_total*100), (string)$item->new_total,2):0;;
            $item->day14_rate=$item->new_total?(float)bcdiv((string)($item->day14_total*100), (string)$item->new_total,2):0;;
            $item->day30_rate=$item->new_total?(float)bcdiv((string)($item->day30_total*100), (string)$item->new_total,2):0;;
            return $item;
        });
        return $this->ChContactMapper->setPaginate($paginate);
    }


    public function roomStatisticsByDay($params)
    {
        $contactBuild = $this->ChContactMapper->model::query()->where('main_uid',$params['main_uid']);
        $paginate=$contactBuild->selectRaw(
            "FROM_UNIXTIME(create_time, '%Y-%m-%d') as date,
            count(*) as new_room_total"
        )   ->groupBy("date")
            ->orderBy("date",'desc')
            ->paginate((int)$params['page_size']);
        $paginate->getCollection()->transform(function ($item)use($params){
            $date = $item->date;
            $startDateTime=Carbon::parse($date)->startOfDay();
            $endDateTime=Carbon::parse($date)->endOfDay();
            $data=$this->ChRoomMemberMapper->model::query()
                ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
                ->where('ch_room.main_uid',$params['main_uid'])
                ->selectRaw(
                    "SUM(CASE WHEN join_time < {$endDateTime->getTimestamp()} then 1 else 0 end) as member_total,
                    SUM(CASE WHEN join_time < {$endDateTime->getTimestamp()} and is_friend = 1 then 1 else 0 end) as new_member_custom_total,
                    SUM(CASE WHEN join_time < {$endDateTime->getTimestamp()} and send_last_time  between {$startDateTime->getTimestamp()} and {$endDateTime->getTimestamp()} then 1 else 0 end) as new_member_active_total,
                    SUM(CASE WHEN out_time between '{$startDateTime->format('Y-m-d H:i:s')}' and '{$endDateTime->format('Y-m-d H:i:s')}' then 1 else 0 end) AS new_out_total,
                    SUM(CASE WHEN join_time between {$startDateTime->getTimestamp()} and {$endDateTime->getTimestamp()} then 1 else 0 end) AS new_member_total",
                )->first()->toArray();
            $data2=$this->ChRoomMapper->model::query()
                ->where('main_uid',$params['main_uid'])
                ->selectRaw(
                    "SUM(CASE WHEN create_time < {$endDateTime->getTimestamp()} then 1 else 0 end) as room_total",
                )->first()->toArray();
            $item->new_member_total=$data['new_member_total'];
            $item->new_out_total=$data['new_out_total'];
            $item->new_member_active_total=$data['new_member_active_total'];
            $item->new_member_custom_total=$data['new_member_custom_total'];
            $item->member_total=$data['member_total'];
            $item->room_total=$data2['room_total'];
            return $item;
        });
        return $this->ChContactMapper->setPaginate($paginate);
    }

    public function customerStatisticsByDay($params)
    {
        $mainUid=$params['main_uid'];
        $range=data_get($params,'range');
        $contactBuild = $this->ChContactMapper->model::query()->where('main_uid',$params['main_uid']);
        $paginate=$contactBuild->selectRaw(
            "FROM_UNIXTIME(add_customer_time, '%Y-%m-%d') as date,
            count(*) as new_total,
            SUM(CASE WHEN FROM_UNIXTIME(last_send_msg_time, '%Y-%m-%d') = FROM_UNIXTIME(create_time, '%Y-%m-%d') then 1 else 0 end) AS active_total,
            SUM(CASE WHEN status = 3 then 1 else 0 end) AS new_loss_total"
        )
            ->when($range,function ($query)use($range){
                $query->whereBetween('add_customer_time',[strtotime($range[0]),strtotime($range[1])]);
            })
            ->groupBy("date")
            ->orderBy("date",'desc')
            ->paginate((int)$params['page_size']);
        $paginate->getCollection()->transform(function ($item)use($params){
            $date=$item['date'];
            $item->total=$this->ChContactMapper->model::query()->where('main_uid',$params['main_uid'])->whereRaw("create_time <= UNIX_TIMESTAMP('$date')")->count();
            $item->loss_total=$this->ChContactMapper->model::query()->where('main_uid',$params['main_uid'])->whereRaw("status = 3 and create_time <= UNIX_TIMESTAMP('$date')")->count();
            return $item;
        });
        return $this->ChContactMapper->setPaginate($paginate);
    }


    protected function getChatRoomData($params,$start,$end)
    {
        $timeInterValues=$this->getTimeIntervals($start,$end);
        foreach ($timeInterValues as $key=>$timeInterValue){
            $where =[];
            if(!empty($params['robot_id'])){
                $where[]=['robot_id',$params['robot_id']];
            }
            $talkRecordBuild = $this->ChTalkRecord::query()->where('main_uid',$params['main_uid'])->where($where)->whereBetween('created_at',[$timeInterValue['start'],$timeInterValue['end']]);
            $send_total=$talkRecordBuild->count();

            $talkRecordBuild = ChTalkSession::query()->where('main_uid',$params['main_uid'])->where($where)->whereBetween('created_at',[$timeInterValue['start'],$timeInterValue['end']]);
            $talkData = $talkRecordBuild->selectRaw(
                "SUM(CASE WHEN is_robot = 1 THEN 1 ELSE 0 END) as robot_chat_total,
             SUM(CASE WHEN is_robot = 0 THEN 1 ELSE 0 END) as custom_chat_total,
             SUM(CASE WHEN contact_num > 0 THEN 1 ELSE 0 END) as reply_total,
             AVG(CASE WHEN contact_first_reply is not null THEN contact_first_reply ELSE NULL END) as reply_time,
             count(1) as chat_total"
            )->first();
            $timeInterValues[$key]['static']=[
                'robot_chat_total'=>$talkData['robot_chat_total']?:0,
                'custom_chat_total'=>$talkData['custom_chat_total']?:0,
                'chat_total'=>$talkData['chat_total']?:0,
                'reply_total'=>$talkData['reply_total']?:0,
                'reply_rate'=>$talkData['chat_total']>0?bcdiv((string)($talkData['reply_total']*100),(string)$talkData['chat_total'],2):0,
                'reply_time'=>$talkData['reply_time']?:0,
                'send_total'=>$send_total,
            ];
        }
        return $timeInterValues;
    }

    protected function getChartRoomData($mainUid,$start,$end)
    {
        $timeInterValues=$this->getTimeIntervals($start,$end);
        foreach ($timeInterValues as $key=>$timeInterValue){
            $s=strtotime($timeInterValue['start']);
            $e=strtotime($timeInterValue['end']);
            $data=$this->ChRoomMemberMapper->model::query()
                ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
                ->where('ch_room.main_uid',$mainUid)
                ->selectRaw(
                    "SUM(CASE WHEN join_time < '$e' then 1 else 0 end) as room_member_total,
                    SUM(CASE WHEN out_time between '{$timeInterValue['start']}' and '{$timeInterValue['end']}' then 1 else 0 end) AS new_out_total,
                SUM(CASE WHEN join_time between '$s' and '$e' then 1 else 0 end) AS new_member_total",
                )->first()->toArray();
            $data2=$this->ChRoomMapper->model::query()
                ->where('main_uid',$mainUid)
                ->selectRaw(
                    "SUM(CASE WHEN create_time < '$e' then 1 else 0 end) as room_total,
                    SUM(CASE WHEN create_time between '$s' and '$e' then 1 else 0 end) AS new_room_total",
                )->first()->toArray();

            $timeInterValues[$key]['static']=[
                'new_member_total'=>$data?$data['new_member_total']:0,
                'room_member_total'=>$data?$data['room_member_total']:0,
                'new_out_total'=>$data?$data['new_out_total']:0,
                'new_room_total'=>$data2?$data2['new_room_total']:0,
                'room_total'=>$data2?$data2['room_total']:0,
            ];
        }
        return $timeInterValues;
    }

    protected function getChartRoomIndexData($mainUid,$start,$end)
    {
        $timeInterValues=$this->getTimeIntervals($start,$end);
        foreach ($timeInterValues as $key=>$timeInterValue){
            $s=strtotime($timeInterValue['start']);
            $e=strtotime($timeInterValue['end']);
            $data=$this->ChRoomMemberMapper->model::query()
                ->leftJoin('ch_room', 'ch_room.room_wxid', '=', 'ch_room_member.room_wxid')
                ->where('ch_room.main_uid',$mainUid)
                //->where('ch_room_member.is_out',0)
                ->selectRaw(
                "SUM(CASE WHEN join_time < '$e' then 1 else 0 end) as room_member_total,
                SUM(CASE WHEN join_time between '$s' and '$e' then 1 else 0 end) AS new_member_total",
            )->first()->toArray();
            $data2=$this->ChRoomMapper->model::query()
                ->where('main_uid',$mainUid)
                ->selectRaw(
                    "SUM(CASE WHEN create_time < '$e' then 1 else 0 end) as room_total,
                    SUM(CASE WHEN create_time between '$s' and '$e' then 1 else 0 end) AS new_room_total"
                )->first()->toArray();
            $timeInterValues[$key]['static']=[
                'room_total'=>(int)$data2['room_total']?:0,
                'room_member_total'=>(int)$data['room_member_total']?:0,
                'new_member_total'=>(int)$data['new_member_total']?:0,
                'new_room_total'=>(int)$data2['new_room_total']?:0
            ];
        }
        return $timeInterValues;
    }

    protected function getChartRoomDataIndex($roomBuild)
    {
        $countValues=$this->getRoomMemberCountRange();
        foreach ($countValues as $key=>$value){
            $min=$value['min'];
            $max=$value['max'];
            $data=$roomBuild->selectRaw(
                "SUM(CASE WHEN room_member_count between $min and $max then 1 else 0 end) AS room_total",
            )->first()->toArray();
            $countValues[$key]['static']=[
                'room_total'=>(int)$data['room_total']?:0,
            ];
        }
        return $countValues;
    }

    protected function getChartRoomActiveRank($mainUid)
    {
        return $this->ChRoomMapper->model::query()->where('main_uid',$mainUid)
            ->orderBy('room_member_count','desc')
            ->select(['room_name','room_member_count','id'])
            ->limit(10)
            ->get();
    }

    protected function getChartDataIndex($query,$start,$end)
    {
        $timeInterValues=$this->getTimeIntervals($start,$end);
        foreach ($timeInterValues as $key=>$timeInterValue){
            $s=strtotime($timeInterValue['start']);
            $e=strtotime($timeInterValue['end']);
            $data=$query->selectRaw(
                "SUM(CASE WHEN create_time < '$e' then 1 else 0 end) as total,
                SUM(CASE WHEN create_time between '$s' and '$e' then 1 else 0 end) AS new_total,
                SUM(CASE WHEN create_time between '$s' and '$e' and status = 3 then 1 else 0 end) AS new_loss_total",
            )->first()->toArray();
            $timeInterValues[$key]['static']=[
                'total'=>(int)$data['total']?:0,
                'new_total'=>(int)$data['new_total']?:0,
                'new_loss_total'=>(int)$data['new_loss_total']?:0,
            ];
        }
        return $timeInterValues;
    }


    protected function getChartData($query,$start,$end)
    {
        $timeInterValues=$this->getTimeIntervals($start,$end);
        foreach ($timeInterValues as $key=>$timeInterValue){
            $s=strtotime($timeInterValue['start']);
            $e=strtotime($timeInterValue['end']);
            $data=$query->selectRaw(
                "SUM(CASE WHEN add_customer_time < $e then 1 else 0 end) AS total,
                 SUM(CASE WHEN add_customer_time between $s and $e then 1 else 0 end) AS new_total,
            SUM(CASE WHEN last_send_msg_time between '{$timeInterValue['start']}' and '{$timeInterValue['end']}' then 1 else 0 end) AS active_total,
            SUM(CASE WHEN friend_loss_time < '{$timeInterValue['end']}'  and status = 3 then 1 else 0 end) AS loss_total,
            SUM(CASE WHEN friend_loss_time between '{$timeInterValue['start']}' and '{$timeInterValue['end']}' and status = 3 then 1 else 0 end) AS new_loss_total",
            )->first()->toArray();
            $timeInterValues[$key]['static']=[
                'new_total'=>$data['new_total']?:0,
                'active_total'=>$data['active_total']?:0,
                'new_loss_total'=>$data['new_loss_total']?:0,
                'total'=>$data['total']?:0,
                'loss_total'=>$data['loss_total']?:0,
            ];
        }
        return $timeInterValues;
    }

    protected function getDataRange(string $time)
    {
        $data = match ($time) {
            'today' => [date('Y-m-d 00:00:00'), date('Y-m-d 23:59:59')],
            'yesterday' => [date('Y-m-d 00:00:00', strtotime('-1 day')), date('Y-m-d 23:59:59', strtotime('-1 day'))],
            'last_7_days' => [date('Y-m-d H:i:s', strtotime('-7 days')), date('Y-m-d 23:59:59')],
            'last_15_days' => [date('Y-m-d H:i:s', strtotime('-15days')),date('Y-m-d 23:59:59')],
            'last_30_days' => [date('Y-m-d H:i:s', strtotime('-30 days')),date('Y-m-d 23:59:59')],
            default => []
        };
        return $data;
    }


    protected function getRoomMemberCountRange()
    {
        $data=[
            [
                'title'=>"0-20人",
                'min'=>0,
                'max'=>20,
            ],
            [
                'title'=>"21-50人",
                'min'=>21,
                'max'=>50,
            ],
            [
                'title'=>"51-100人",
                'min'=>51,
                'max'=>100,
            ],
            [
                'title'=>"101-300人",
                'min'=>101,
                'max'=>300,
            ],
            [
                'title'=>"301-500人",
                'min'=>301,
                'max'=>500,
            ]
        ];
        return $data;
    }

    /**
     * 根据时间跨度返回相应的间隔时间，包含详细的时间信息
     *
     * @param  $start
     * @param  $end
     * @return array 间隔时间数组
     */
    public function getTimeIntervals($start, $end) {
        $start = Carbon::parse($start);
        $end = Carbon::parse($end);
        $intervals = [];

        // 计算时间差（秒）
        $diffSeconds = $end->getTimestamp() - $start->getTimestamp();
        $diffDays = $diffSeconds / (24 * 60 * 60);

        if ($diffDays < 1) {
            // 小于一天，返回24小时各自的间隔时间
            $current = clone $start;
            $now=Carbon::now();
            for ($i = 0; $i < 24; $i++) {
                $next = clone $current;
                $next->modify('+1 hour');
                if ($next > $end || $current>$now) {
                    break;
                }
                $tempCurrent=clone $current;
                $intervals[] = [
                    'date'=>(int)$tempCurrent->format('H').'点',
                    'start' => $tempCurrent->format('Y-m-d H:i:s'),
                    'end' => $next->format('Y-m-d H:i:s'),
                ];
                $current = $next;
            }
        } elseif ($diffDays <= 31) {
            // 大于等于1天且小于等于30天，返回每天的间隔时间
            $current = clone $start;
            while ($current < $end) {
                $next = clone $current;
                $next->modify('+1 day');
                if ($next > $end) {
                    $next = $end;
                }
                $tempCurrent=clone $current;
                $intervals[] = [
                    'date' => $tempCurrent->format('m-d'),
                    'start' => $tempCurrent->format('Y-m-d H:i:s'),
                    'end' => $next->format('Y-m-d H:i:s'),
                ];
                $current = $next;
            }
        } else {
            // 大于一个月，返回每个月的间隔时间
            $current = clone $start;
            while ($current < $end) {
                $next = clone $current;
                $next->modify('+1 month');
                if ($next > $end) {
                    $next = $end;
                }
                $tempCurrent=clone $current;
                $intervals[] = [
                    'date' => $tempCurrent->format('m')."月",
                    'start' => $tempCurrent->format('Y-m-d H:i:s'),
                    'end' => $next->format('Y-m-d H:i:s'),
                ];
                $current = $next;
            }
        }

        return $intervals;
    }

    protected function getCropTagData($mainUid)
    {
        $tagConstants=Db::table('ch_corp_tag')
            ->leftJoin('ch_corp','ch_corp_tag.corp_id','=','ch_corp.id')
            ->where('ch_corp.main_uid',$mainUid)
            ->select(['ch_corp_tag.id as value','ch_corp_tag.name as label'])
            ->limit(100)
            ->get();
        $newTagData=[];
        $other_total=0;
        if($tagConstants->isNotEmpty()){
            $tagConstants[]=[
              'value'=>'9999',
              'label'=>'其他',
            ];
            $tagIds=$tagConstants->pluck('value');
            $tagData=Db::table('ch_contact_corp_tag')->selectRaw(
                'corp_tag_id,count(1) as tag_total'
            )->whereIn('corp_tag_id',$tagIds)->get();

            if($tagData->isNotEmpty()){
                foreach ($tagData as $key=>$item){
                    if($key < 4){
                        $newTagData[]=$item;
                    }else{
                        foreach ($tagConstants as $key2=>$source_item){
                            if($source_item['value']==$item['corp_tag_id']){
                                unset($tagConstants[$key2]);
                            }
                        }
                        $other_total+=$item['source_total'];
                    }
                }
            }
            $other_total && $newTagData[]=['source'=>9999,'source_total'=>$other_total];
        }
        return [
            'tags_data'=>$newTagData,
            'tags_constants'=>$tagConstants->toArray()?:[],
        ];
    }

    protected function getContactSourceData($sourceQuery)
    {
        $sourceConstants=ContactSourceConstants::getOptions();
        //查询渠道分布
        $sourceData=$sourceQuery->selectRaw(
            "source,count(1) as source_total",
        )->groupBy('source')
            ->orderBy('source_total', 'desc')
            ->get();
        $newSourceData=[];
        $other_source_total=0;
        if($sourceData->isNotEmpty()){
            foreach ($sourceData as $key=>$item){
                if($key < 4){
                    $newSourceData[]=$item;
                }else{
                    foreach ($sourceConstants as $key2=>$source_item){
                        if($source_item['value']==$item['source']){
                            unset($sourceConstants[$key2]);
                        }
                    }
                    $other_source_total+=$item['source_total'];
                }
            }
        }
        $other_source_total && $newSourceData[]=['source'=>ContactSourceConstants::OTHER,'source_total'=>$other_source_total];
        return [
            'source_data'=>$newSourceData,
            'source_constants'=>$sourceConstants,
        ];
    }



}